package org.traffometer.traffobase.model;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.traffometer.common.data.TraffoPacket;
import org.traffometer.opendmtp.protocol.GeoPoint;

/**
 * Represents a PacketStore that saves its contents to disk as a CSV file.
 * 
 * @author Mark Stillwell 
 */
public class FilePacketStore implements PacketStore {

  /**
   * A List of TraffoPackets.
   */
  private final List<TraffoPacket> packets;
  
  /**
   * The path to a file used to store packets.
   */
  private final String packetFileDir;
  private File packetFile; //NOPMD
  private String packetFileName = "packetStore.db";//NOPMD
  
  /**
   * Creates a FilePacketStore that uses the given file to store its internal state.
   * 
   * @param packetFilePath the path to a file that can be used to save packets
   */
  public FilePacketStore(String packetFilePath) {
    
    this.packetFileDir = packetFilePath;
    this.packetFile = new File(this.packetFileDir + "/" + this.packetFileName);
    this.packets = new ArrayList<TraffoPacket>();
    
    if (packetFile.isDirectory()) {
      
      restorePackets();
    }
    else {
      
      new File(this.packetFileDir).mkdirs();

      try {
//        new org.apache.derby.jdbc.ClientDriver();
        Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
        Connection conn = DriverManager.getConnection("jdbc:derby://localhost:1527/" 
                                                     + packetFileDir + "/"
                                                     + this.packetFileName + ";create=true");
        conn.setAutoCommit(false);
        Statement s = conn.createStatement();
        s.execute("create table packetStore.db(index int, " +
                                              "lat1 decimal(20,15), " +
                                              "lng1 decimal(20,15), " +
                                              "time1 int, " +
                                              "lat2 decimal(20,15), " +
                                              "lng2 decimal(20,15), " +
                                              "time2 int, " +
                                              "veh varchar(15))");
        s.close();
        conn.commit();
        conn.close();
/*        try{
          DriverManager.getConnection("jdbc:derby:" + packetFileDir + "/"
            + this.packetFileName + ";shutdown=true");
        }
        catch(Exception e){
          System.out.println("Database has been shutdown");
        }
*/        
      }
      catch (Exception e) {
        e.printStackTrace();
      }
      savePackets();
    }
  }
  
  /**
   * Save packets to the disk.
   */
  private void savePackets() {
    
    try {
//      new org.apache.derby.jdbc.ClientDriver();
      Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
      Connection conn = DriverManager.getConnection("jdbc:derby://localhost:1527/" 
          + this.packetFileDir + "/"
          + this.packetFileName + ";create=true");
      conn.setAutoCommit(false);
      Statement s = conn.createStatement();
      s.execute("drop table packetStore.db");
      s.execute("create table packetStore.db(index int, " +
                                              "lat1 decimal(20,15), " +
                                              "lng1 decimal(20,15), " +
                                              "time1 int, " +
                                              "lat2 decimal(20,15), " +
                                              "lng2 decimal(20,15), " +
                                              "time2 int, " +
                                              "veh varchar(15))");

      int j = 1;
      for (TraffoPacket packet : this.packets) {
        s.execute("insert into packetStore.db values (" + j + "," + 
                                                packet.getLatitude1() + "," +
                                                packet.getLongitude1() + "," +
                                                packet.getTimeStamp1() + "," +
                                                packet.getLatitude2() + "," + 
                                                packet.getLongitude2() + "," + 
                                                packet.getTimeStamp2() + ",'" +
                                                packet.getVehicleType() + "')");
        j++;
      }
      s.close();
      conn.commit();
      conn.close();
/*      try{
        DriverManager.getConnection("jdbc:derby:" + packetFileDir + "/"
          + this.packetFileName + ";shutdown=true");
      }
      catch(Exception e){
        System.out.println("Database has been shutdown");
      }
*/      
    }
    catch (Exception e) {
      System.out.println("exception while saving packets. See log. " + e);
    }
  }
  
  /**
   * Restore packets from the disk.
   */
  private void restorePackets() {
    this.packets.clear();
    try {
      double lat1, long1, lat2, long2;
      long time1, time2;
      String vehicleType;
//      new org.apache.derby.jdbc.ClientDriver();
      Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
      Connection conn = DriverManager.getConnection("jdbc:derby://localhost:1527/" 
          + packetFileDir + "/"
          + this.packetFileName);
      conn.setAutoCommit(false);
      Statement s = conn.createStatement();
      ResultSet rs = s.executeQuery("SELECT * FROM packetStore.db ORDER BY index");
      while (rs.next()) {
        lat1 = rs.getDouble("lat1");
        long1 = rs.getDouble("lng1");
        time1 = rs.getLong("time1");
        lat2 = rs.getDouble("lat2");
        long2 = rs.getDouble("lng2");
        time2 = rs.getLong("time2");
        vehicleType = rs.getString("veh");
        this.packets.add(
            new TraffoPacket(
                new GeoPoint(lat1, long1, time1), 
                new GeoPoint(lat2, long2, time2),
                vehicleType
            )
        );
      }
      rs.close();
      s.close();
      conn.commit();
      conn.close();
/*      try{
        DriverManager.getConnection("jdbc:derby:" + packetFileDir + "/"
          + this.packetFileName + ";shutdown=true");
      }
      catch(Exception e){
        System.out.println("Database has been shutdown");
      }
*/      
    }
    catch (Exception e) {
      System.out.println("exception while restoring packets. See log. " + e);
    }
  }

  /**
   * Adds a packet to this PacketStore.  First restores packets from the file, then adds a packet,
   * then saves the packets back to the disk.  This method does not do any file locking, so 
   * conflicts are possible.
   * 
   * @param packet a packet to add
   * @return true if the packet is successfully added.
   * @see org.traffometer.traffobase.model.PacketStore#add(org.traffometer.common.data.TraffoPacket)
   */
  public boolean add(TraffoPacket packet) {
    boolean retVal;
    restorePackets();
    retVal = this.packets.add(packet);
    savePackets();
    return retVal;
  }

  /**
   * Removes the packets from this FilePacketStore, then overwrites the packetFile with the
   * empty data structure.
   * 
   * @see org.traffometer.traffobase.model.PacketStore#clear()
   */
  public void clear() {
    this.packets.clear();
    savePackets();
  }

  /**
   * Returns an iterator over this FilePacketStore. First restores from the disk in case 
   * another process has made changes.
   * 
   * @return an iterator over this FilePacketStore
   * @see org.traffometer.traffobase.model.PacketStore#iterator()
   */
  public Iterator<TraffoPacket> iterator() {
    restorePackets();
    return this.packets.iterator();
  }

  /**
   * Getter for list of TraffoPackets.
   * 
   * @return the packets
   */
  public List<TraffoPacket> getPackets() {
    return packets;
  }
}